package com.dao;

import com.domain.Equipment;
import com.domain.Equipment_type;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EquipmentDao {


    /**
     * 添加设备
     * @param equipment
     */
    @Insert("insert into equipment (equipment_name, equipment_type, description, value, activation_time, " +
            "regular_maintenance_time, equipment_photos, user_id) values( #{equipment_name}, " +
            "#{equipment_type}, #{description},#{value},#{activation_time}, #{regular_maintenance_time}," +
            " #{equipment_photos}, #{user_id})")
    public void addEquipment(Equipment equipment);

    /**
     * 根据设备管理员id查询设备
     * @param user_id
     * @return
     */
    @Select("select equipment.*, user.name, equipment_type.type_name " +
            "from equipment, user, equipment_type " +
            "where equipment.user_id = #{user_id} and equipment.user_id = user.id and " +
            "equipment_type.type_id = equipment.equipment_type")
    public List<Equipment> findByUser_id(Integer user_id);

    /**
     * 查询所有设备
     * @return
     */
    @Select("select equipment.*, user.name, equipment_type.type_name " +
            "from equipment, user, equipment_type " +
            "where equipment.user_id = user.id and " +
            "equipment_type.type_id = equipment.equipment_type and " +
            "equipment.equipment_id != 3 and equipment.equipment_id != 6")
    public List<Equipment> findAllEquipment();

    /**
     * 根据id查询设备
     * @param id
     * @return
     */
    @Select("select equipment.*, user.name, equipment_type.type_name " +
            "from equipment, user, equipment_type " +
            "where equipment.user_id = user.id and " +
            "equipment.equipment_id = #{id} and " +
            "equipment_type.type_id = equipment.equipment_type")
    public Equipment findOneById(Integer id);


    /**
     * 查询所有设备类型
     * @return
     */
    @Select("select * from equipment_type")
    public List<Equipment_type> findAllType();

    /**
     * 修改设备信息
     * @param equipment
     */
    @Update("update equipment set equipment_name = #{equipment_name}, equipment_type = #{equipment_type}, description = #{description}, maintenance_times = #{maintenance_times}, " +
            "value = #{value}, activation_time = #{activation_time}, regular_maintenance_time = #{regular_maintenance_time}, equipment_photos = #{equipment_photos}, user_id = #{user_id} " +
            "where equipment_id = #{equipment_id}")
    public void updateEquipment(Equipment equipment);

    /**
     * 删除设备
     * @param id
     */
    @Delete("delete from equipment where equipment_id = #{id}")
    public void delEquipment(Integer id);

    /**
     * 更改设备状态
     * @param equipment_id
     * @param state_id
     */
    @Update("update equipment set state_id = #{state_id} where equipment_id = #{equipment_id}")
    public void updateEquipmentState(@Param("equipment_id") Integer equipment_id, @Param("state_id") Integer state_id);

    /**
     * 查询某年某月某设备的维修金额
     * @param equipment_id
     * @return
     */
    @Select("select sum(final_price) from maintenance_order, maintenance_process, equipment " +
            "where equipment.equipment_id = #{equipment_id} and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_process.order_id = maintenance_order.id and maintenance_process.task_id = 1 and " +
            "date_format(maintenance_process.finished_time,'%Y-%m') = #{time}")
    public Float priceEquipment(@Param("equipment_id")Integer equipment_id, @Param("time")String time);

}
